{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm=pd.read_csv('STK_LISTEDCOINFOANL.csv')\n",
    "firm=firm.dropna()\n",
    "#删除房地产、金融企业\n",
    "firm = firm.drop(firm[(firm['IndustryCodeD'] == 'J66') | (firm['IndustryCodeD'] == 'K70')].index)\n",
    "#删除状态异常企业\n",
    "firm = firm[firm['LISTINGSTATE'] == '正常上市']\n",
    "#删除在此期间上市企业\n",
    "firm['LISTINGDATE'] = pd.to_datetime(firm['LISTINGDATE'])\n",
    "firm = firm[firm['LISTINGDATE'] < '2018-04-27']\n",
    "firm = firm.rename(columns={'Symbol': 'Stkcd','EndDate': 'Accper'})\n",
    "firm=firm[['Stkcd','Accper','PROVINCECODE']]\n",
    "firm['Accper']=pd.to_datetime(firm['Accper'])\n",
    "firmad = firm.set_index(['Stkcd', 'Accper'])\n",
    "#firmad"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "自变量计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "xdata=pd.read_csv('FS_Combas.csv')\n",
    "xdata=xdata[xdata['Typrep']=='A']\n",
    "xdata['Accper']=pd.to_datetime(xdata['Accper'])\n",
    "def fill_missing_with_zero_if_any_present(row):\n",
    "    if row.notnull().any():  # 检查行中是否有非缺失值\n",
    "        return row.fillna(0)  # 如果有，将缺失值填充为0\n",
    "    else:\n",
    "        return row  # 如果没有（虽然在这个特定场景中不会发生），返回原行\n",
    "# 应用这个函数到DataFrame的每一行\n",
    "xdata = xdata.apply(fill_missing_with_zero_if_any_present, axis=1)\n",
    "xdata = xdata.set_index(['Stkcd', 'Accper'])\n",
    "xdata['Fin']=(xdata['A001107000']+xdata['A0f1122000']+xdata['A001202000']+xdata['A001203000']+xdata['A001211000'])/(xdata['A001000000'])\n",
    "xdata=xdata[['Fin']]\n",
    "xdata=xdata.dropna()\n",
    "#xdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.merge(firmad,xdata,on=['Stkcd', 'Accper'],how='left')\n",
    "d=d.dropna()\n",
    "#d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因变量导入——研发支出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "RD=pd.read_csv('PT_LCRDSPENDING.csv')\n",
    "RD=RD.dropna()\n",
    "RD = RD[(RD['Source'] == 0) & (RD['StateTypeCode'] == 1)]\n",
    "RD = RD.rename(columns={'Symbol': 'Stkcd','EndDate': 'Accper'})\n",
    "RD=RD[['Stkcd','Accper','RDSpendSumRatio']]\n",
    "RD['Accper']=pd.to_datetime(RD['Accper'])\n",
    "RD = RD.set_index(['Stkcd', 'Accper'])\n",
    "#RD\n",
    "d1=pd.merge(d,RD,on=['Stkcd', 'Accper'],how='left')\n",
    "d1=d1.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因变量导入——非效率投资"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "II=pd.read_csv('BDT_InefficInvest.csv')\n",
    "II=II.dropna()\n",
    "II = II[(II['STPT'] == 1) & (II['IsNewOrSuspend'] == 1) & (II['ISBSE'] == 0)]\n",
    "II = II.rename(columns={'Symbol': 'Stkcd','Enddate': 'Accper'})\n",
    "II=II[['Stkcd','Accper','InefficInvestDegree']]\n",
    "II['Accper']=pd.to_datetime(II['Accper'])\n",
    "II = II.set_index(['Stkcd', 'Accper'])\n",
    "d2=pd.merge(d1,II,on=['Stkcd', 'Accper'],how='left')\n",
    "d2=d2.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——企业规模"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "size=pd.read_csv('EVA_Structure.csv')\n",
    "size=size.dropna()\n",
    "size = size.rename(columns={'Symbol': 'Stkcd','EndDate': 'Accper'})\n",
    "size=size[['Stkcd','Accper','MarketValue']]\n",
    "size['Accper']=pd.to_datetime(size['Accper'])\n",
    "size = size.set_index(['Stkcd', 'Accper'])\n",
    "d3=pd.merge(d2,size,on=['Stkcd', 'Accper'],how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——资产负债率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "lev=pd.read_csv('FI_T1.csv')\n",
    "lev=lev.dropna()\n",
    "lev=lev[lev['Typrep']=='A']\n",
    "lev = lev.rename(columns={'F011201A': 'lev'})\n",
    "lev=lev[['Stkcd','Accper','lev']]\n",
    "lev['Accper']=pd.to_datetime(lev['Accper'])\n",
    "lev = lev.set_index(['Stkcd', 'Accper'])\n",
    "d4=pd.merge(d3,lev,on=['Stkcd', 'Accper'],how='left')\n",
    "d4=d4.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——ROE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "ROE=pd.read_csv('FI_T5.csv')\n",
    "ROE=ROE.dropna()\n",
    "ROE=ROE[ROE['Typrep']=='A']\n",
    "ROE = ROE.rename(columns={'F050501B': 'ROE'})\n",
    "ROE=ROE[['Stkcd','Accper','ROE']]\n",
    "ROE['Accper']=pd.to_datetime(ROE['Accper'])\n",
    "ROE = ROE.set_index(['Stkcd', 'Accper'])\n",
    "d5=pd.merge(d4,ROE,on=['Stkcd', 'Accper'],how='left')\n",
    "d5=d5.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——现金比率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "ca=pd.read_csv('FI_T3.csv')\n",
    "ca=ca.dropna()\n",
    "ca=ca[ca['Typrep']=='A']\n",
    "ca = ca.rename(columns={'F030201A': 'cash'})\n",
    "ca=ca[['Stkcd','Accper','cash']]\n",
    "ca['Accper']=pd.to_datetime(ca['Accper'])\n",
    "ca = ca.set_index(['Stkcd', 'Accper'])\n",
    "d6=pd.merge(d5,ca,on=['Stkcd', 'Accper'],how='left')\n",
    "d6=d6.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——ocf、q、age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "q=pd.read_csv('q.csv')\n",
    "q=q.dropna()\n",
    "q = q[(q['STPT'] == 1) & (q['IsNewOrSuspend'] == 1) & (q['ISBSE'] == 0)]\n",
    "q = q.rename(columns={'Symbol': 'Stkcd','Enddate': 'Accper'})\n",
    "q=q[['Stkcd','Accper','GrowthOpportunity','CashFlowStatus','ListingAge','StockYield']]\n",
    "q['Accper']=pd.to_datetime(q['Accper'])\n",
    "q = q.set_index(['Stkcd', 'Accper'])\n",
    "d7=pd.merge(d6,q,on=['Stkcd', 'Accper'],how='left')\n",
    "d7=d7.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——资本密集度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "ci=pd.read_csv('FI_T4.csv')\n",
    "ci=ci.dropna()\n",
    "ci=ci[ci['Typrep']=='A']\n",
    "ci = ci.rename(columns={'F041601B': 'CI'})\n",
    "ci=ci[['Stkcd','Accper','CI']]\n",
    "ci['Accper']=pd.to_datetime(ci['Accper'])\n",
    "ci = ci.set_index(['Stkcd', 'Accper'])\n",
    "d8=pd.merge(d7,ci,on=['Stkcd', 'Accper'],how='left')\n",
    "d8=d8.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "控制变量导入——股权性质"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "e=pd.read_csv('EN_EquityNatureAll.csv')\n",
    "e=e.dropna()\n",
    "e = e.rename(columns={'Symbol': 'Stkcd','EndDate': 'Accper'})\n",
    "e['EquityNatureID'] = e['EquityNatureID'].apply(lambda x: 1 if x == 1 else 0)\n",
    "e=e[['Stkcd','Accper','LargestHolderRate','EquityNatureID']]\n",
    "e['Accper']=pd.to_datetime(e['Accper'])\n",
    "e = e.set_index(['Stkcd', 'Accper'])\n",
    "d9=pd.merge(d8,e,on=['Stkcd', 'Accper'],how='left')\n",
    "d9=d9.dropna()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
